package com.skyolder.milkbee.repository;

import com.skyolder.milkbee.domain.Card;
import com.skyolder.milkbee.dto.HomeCard;
import com.skyolder.milkbee.dto.MonthGroupResult;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.*;

import java.time.ZonedDateTime;
import java.util.List;

/**
 * Spring Data JPA repository for the Card entity.
 */
public interface CardRepository extends JpaRepository<Card, Long> {

	Page<Card> findByCategoryId(Long categoryId, Pageable pageable);

	@Query("select new  com.skyolder.milkbee.dto.HomeCard(c.id,c.recommendTitle,c.createdAt,c.author,c.commentCount, ca.name) from Card c,Category ca where c.categoryId=ca.id order by c.recommend desc,c.id desc")
	List<HomeCard> doQueryCardRecommends(Pageable pageable);

	@Query("select new  com.skyolder.milkbee.dto.HomeCard(c.id,c.recommendTitle,c.createdAt,c.author,c.commentCount, ca.name) from Card c,Category ca where c.categoryId=ca.id order by c.essence desc,c.id desc")
	List<HomeCard> doQueryCardEssences(Pageable pageable);

	Page<Card> findByUserId(Long userId, Pageable pageable);

	@Query("from Card c where c.accusation>0 and c.userId in(?1) order by c.recommend desc,c.id desc")
	Page<Card> doQueryAccusationByModerator(List<Long> userIds, Pageable pageable);

	@Query("select new  com.skyolder.milkbee.dto.MonthGroupResult(c.userId,c.leaguerId,c.createdAt,count(c.id),sum(c.light)) from Card c where c.leaguerId in(?1)  group by c.leaguerId, month(c.createdAt)")
	List<MonthGroupResult> doGroupByleaguersAndMonth(List<Long> leids);

	@Query("select new  com.skyolder.milkbee.dto.MonthGroupResult(c.userId,c.leaguerId,c.createdAt,count(c.id),sum(c.light)) from Card c where c.leaguerId in(?1)  and c.createdAt between ?2 and ?3 group by c.leaguerId")
	List<MonthGroupResult> doGroupMonth(List<Long> leids, ZonedDateTime beginer, ZonedDateTime ender);

	@Query("from Card c where c.essence=2 and c.userId in(?1) ")
	Page<Card> doQueryApplyByModerator(List<Long> mid, Pageable pageable);

	List<Card> findByUserIdAndIdIn(Long userId, List<Long> ids);

	@Query("from Card c where c.accusation>0")
	Page<Card> queryReport(Pageable pageable);

	@Query("from Card c where c.recommend is true")
	Page<Card> queryHome(Pageable pageable);

	@Query("from Card c where c.essence=3")
	Page<Card> queryEssence(Pageable pageable);

	@Query("from Card c where c.essence=2")
	Page<Card> queryApplyEssence(Pageable pageable);

	@Query("from Card c where c.essence=1")
	Page<Card> queryDenyEssence(Pageable pageable);

	@Query("from Card c where length(c.attachment)>3")
	Page<Card> queryAttachment(Pageable pageable);

	Page<Card> findByUserIdIn(List<Long> them, Pageable pageable);

}
